CREATE Procedure [dbo].[Sp_Auto_OURow_Factor] @KindFlag Smallint, @FactorId BIGINT, @PersonPercent REAL  
As 

Declare @AutoId Bigint
Declare @Value Float
Declare @MaxPercent Real
Declare @MaxPrice Money
Declare @TotPrice Money
Declare @TaxValue Money
Declare @CachePrice Money
Declare @Qty Money
Declare @GoodsPrice Money
Declare @TaxFlag Smallint
Declare @TmpPrice Money
Declare @TmpPer Real
Declare @Price MONEY
Declare @Percent Real
Declare @AutoInc UNIQUEIDENTIFIER
Declare @ShowPrice Char(1)
Declare @PriceRow Char(1)

DECLARE @CalcIncrementalDeductionRow AS TABLE
(
   Id UNIQUEIDENTIFIER DEFAULT(NEWID()),
   GoodsId BIGINT,
   Autoid BIGINT,
   Name NVARCHAR(30),
   Type CHAR(1),
   Max_Percent REAL,
   Max_price MONEY,
   ShowPrice CHAR(1),
   Price_Row CHAR(1),
   Price MONEY DEFAULT(0),
   [Percent] REAL DEFAULT(0),
   Qty MONEY,
   GoodsPrice MONEY,
   TaxFlag CHAR(1)
)

INSERT Into @CalcIncrementalDeductionRow (GoodsId, AutoId, Name, Type, Max_Percent, Max_Price, ShowPrice, Price_Row, Qty,GoodsPrice, TaxFlag)
SELECT dbo.TmpHavaleh.AutoId GoodsId,
       Over_Under.AutoId,
       Name,
       Type,
       CASE
          WHEN ShowPrice = 2 THEN  @PersonPercent
          WHEN TaxPercent <> 0      AND ISNULL(Formula,'0') =1 AND Type = 1 THEN TaxPercent 
		  WHEN DiscountPercent <> 0 AND ISNULL(Formula,'0') =1 AND Type = 0 THEN DiscountPercent
          ELSE Max_Percent
       END AS Max_Percent,
       Max_Price,
       ShowPrice,
       CASE WHEN ISNULL(Formula,'0') = 0 THEN 0 ELSE 1 
       END AS Price_Row,
       K_Qty1,
       CASE
           WHEN Kind_Flag IN ( 5, 7, 9, 6, 0 ) THEN
               Price_Forosh
           WHEN Kind_Flag IN ( 2, 3, 4, 8 ) THEN
               Price_KHarid
       END AS Price,
       Takhfif_Flag
FROM TmpHavaleh WITH (NOLOCK)
    INNER JOIN TmpHeadH WITH (NOLOCK)
        ON TmpHeadH.Id_Havaleh = dbo.TmpHavaleh.Id_Havaleh
    INNER JOIN dbo.KalaId WITH (NOLOCK)
        ON KalaId.K_Code = TmpHavaleh.K_Code
    CROSS JOIN dbo.Over_Under WITH (NOLOCK)
WHERE TmpHeadH.Id_Havaleh = @FactorId
      AND Active = 1
      AND Action_Code = @KindFlag
      AND CONVERT(VARCHAR(10), GETDATE(), 111)
      BETWEEN AzTarikh AND TaTarikh
ORDER BY GoodsId,
         SortDisplay;

Declare CursorIncrementalDeductionRow Cursor For
SELECT GoodsId, Qty , GoodsPrice, TaxFlag FROM @CalcIncrementalDeductionRow
GROUP BY GoodsId, Qty , GoodsPrice, TaxFlag
Open CursorIncrementalDeductionRow
Fetch Next From CursorIncrementalDeductionRow Into  @AutoId, @Qty , @GoodsPrice,@TaxFlag
While @@FETCH_STATUS = 0 
Begin
    Select @TaxValue = @Qty * @GoodsPrice * @TaxFlag
    Select @TotPrice = @Qty * @GoodsPrice  

	Declare Cursor_TmpOverUnderRow Cursor For
    SELECT Id, Max_Percent, Max_Price, Price, [percent] [PercentValue], ShowPrice, Price_Row From @CalcIncrementalDeductionRow Where GoodsId = @AutoId
	AND ShowPrice IN(1,2)
	Open Cursor_TmpOverUnderRow
	Fetch Next From Cursor_TmpOverUnderRow Into @AutoInc, @MaxPercent, @MaxPrice , @Price, @Percent, @ShowPrice, @PriceRow
	while @@FETCH_STATUS = 0 
	Begin
		IF @PriceRow = 0
			SET @CachePrice = @TotPrice
		else
			SET @CachePrice = @TaxValue

		IF @MaxPrice > 0
		BEGIN
            IF (ISNULL(@Price,0) > @MaxPrice)
			BEGIN  				
				IF @CachePrice = 0 SET @CachePrice = 1
				SET @TmpPer = @MaxPrice * 100/@CachePrice

				IF @ShowPrice = 2 
					UPDATE @CalcIncrementalDeductionRow SET [Percent] = @TmpPer,Price = ROUND(@MaxPrice,0) WHERE Id = @AutoInc
                ELSE 
					UPDATE @CalcIncrementalDeductionRow Set [Percent] = @TmpPer, Price = ROUND(@CachePrice,0) WHERE Id = @AutoInc
            END
			ELSE
			BEGIN
				IF (ISNULL(@Percent,0) = 0) OR (ISNULL(@Price,0) = 0)
				BEGIN 
				IF @CachePrice = 0 SET @CachePrice = 1
				SET @TmpPer = @MaxPrice * 100/@CachePrice
				UPDATE @CalcIncrementalDeductionRow Set [Percent] = @TmpPer, Price = ROUND(@MaxPrice,0) WHERE Id = @AutoInc
				END 
			END  
		END 
		ELSE IF @MaxPercent > 0 
		BEGIN
			IF (ISNULL(@Percent,0) > @MaxPercent)
			BEGIN  
				SET @TmpPrice = (@CachePrice * @MaxPercent/100)  
				UPDATE @CalcIncrementalDeductionRow Set [Percent] = @MaxPercent, Price = ROUND(@TmpPrice,0) WHERE Id = @AutoInc
            END
			ELSE 
            BEGIN
				IF (ISNULL(@Percent,0) = 0) OR (ISNULL(@Price,0) = 0)
				BEGIN 
				SET @TmpPrice = (@CachePrice * @MaxPercent/100)  
				UPDATE @CalcIncrementalDeductionRow Set [Percent] = @MaxPercent, Price = ROUND(@TmpPrice,0) WHERE Id = @AutoInc
				END 
            END
		END
		Fetch Next From Cursor_TmpOverUnderRow Into @AutoInc, @MaxPercent, @MaxPrice , @Price, @Percent, @ShowPrice, @PriceRow
	End 
	Close Cursor_TmpOverUnderRow
	Deallocate Cursor_TmpOverUnderRow
	/**********************************************************************/    
    INSERT INTO dbo.Tmp_Over_Under_Row(AutoId, Name, Type, Code_Over_Under, Max_Percent, Max_Price, ShowPrice, Price_Row,Price,[Percent])
    SELECT GoodsId ,Name, Type, AutoId, Max_Percent, Max_Price, ShowPrice, Price_Row,Price,[Percent] FROM @CalcIncrementalDeductionRow
	WHERE GoodsId = @AutoId
	AND AutoId NOT IN(SELECT Code_Over_Under FROM Tmp_Over_Under_Row WHERE AutoId = @AutoId)

	Exec dbo.Sp_Duty_Row @FactorId, @AutoId, @KindFlag
    /**********************************************************************/    
    Fetch Next From CursorIncrementalDeductionRow Into @AutoId, @Qty , @GoodsPrice,@TaxFlag
End
Close CursorIncrementalDeductionRow
Deallocate CursorIncrementalDeductionRow
